#! /usr/bin/python
# vi:ts=3
import time
import re
import pdb

# modular select statements
#  descr priority modtime difficulty estimate 
#  worked hoursworked workframe rawwork due 
#  schedule startstop active done 
#  timetospare timefactor priorityfactor 

def mergeselect(slist,range=None,tags=None,where=None,order=None,limit=None):

	ordered = {}
	selcolumns = []
	selfroms   = []
	if type(slist) == str:
		slist = [slist]
	for s in slist:
		deps = eval(s+".requires")
		if len(deps) > 0:
			for d in deps:
				dpl = int(eval(d+".place"))
				ordered[dpl] = d
		pl = int(eval(s+".place"))
		ordered[pl] = s;

	skeys = ordered.keys()
	skeys.sort()
	for b in skeys:
		selcolumns.append(eval(ordered[b]+".selcols"))
		try:
			selfroms.append(eval(ordered[b]+".selfrom"))
		except:
			pass

	rv  = "select"
	rv += ",".join(selcolumns)
	rv += "         from"
	rv += " ".join(selfroms)
	if tags:
		if type(tags) == list:
			tagwh = "where id in (select taskid from tags where "
			tagls = []
			for t in tags:
				tagls.append("tag like '"+t+"'")
			tagwh += " or ".join(tagls)
			tagwh += ")"
		elif type(tags) == str:
			tagwh = "where id in (select taskid from tags where tag like ";
			tagwh += "'"+tags+"'"
			tagwh += ")"
		rv += " " + tagwh
	if where:
		if tags:
			rv += " " + where.replace("where"," and",1)
		else:
			rv += " " + where
	if order:
		rv += " " + order
	if limit:
		rv += " " + limit

	rv = re.sub("_CURRENT_EPOCH_","%d" % time.time(),rv)
	if range:
		rv = re.sub("_STARTTIME_","%d" % range[0],rv)
		rv = re.sub("_ENDTIME_","%d" % range[1],rv)
	
	return rv
	
class select:
	
	requires = []
	place = 1
	selcols = """
		tasks.id, 
		tasks.name"""

	selfrom = """
		tasks
	"""

class descr:

	requires = ["select"]
	place = 2
	selcols = """
		tasks.descr
	"""

class priority:

	requires = ["select"]
	place = 10
	selcols = """
			CASE WHEN priority.value is null 
				THEN 9 
				ELSE priority.value 
			END 
	pri
	"""

	selfrom = """
			left join (
			select 
				taskid, 
				attr, 
				value 
			from attrs 
			where attr = 'priority') 
		priority 
		on id = priority.taskid 
	"""

class modtime:

	requires = ["select"]
	place = 20
	selcols = """
		tasks.entrytime, 
		tasks.modtime
	"""

class difficulty:

	requires = ["select"]
	place = 30
	selcols = """
		difficulty.value 
	"""

	selfrom = """
			left join (
			select 
				taskid, 
				attr, 
				value 
			from attrs 
			where attr = 'difficulty') 
		difficulty 
		on id = difficulty.taskid 
	"""

class estimate:

	requires = ["select"]
	place = 40
	selcols = """
		estimate.time 
	"""

	selfrom = """
	left join (
	select 
		taskid, 
		entrytime, 
		tag, 
		time 
	from times 
	where tag = 'estimate') 
	estimate 
	on id = estimate.taskid 
	"""

class worked:

	requires = ["select"]
	place = 50
	selcols = """
		worked.time 
	"""

	selfrom = """
	left join (
	select 
		taskid, 
			sum(CASE 
				WHEN tag='stop' 
				THEN time-_CURRENT_EPOCH_ 
				ELSE _CURRENT_EPOCH_-time 
			END) 
		time 
	from times 
	where (tag='start' or tag='stop') 
	group by taskid) 
	worked 
	on id = worked.taskid 
	"""

class hoursworked:

	requires = ["select"]
	place = 51
	selcols = """
		CASE
			WHEN hoursworked.time is null
			THEN 0
			ELSE hoursworked.time
		END hoursworked
	"""

	selfrom = """
	left join (
	select 
		taskid, 
			sum(CASE 
				WHEN tag='stop' 
				THEN time-_CURRENT_EPOCH_ 
				ELSE _CURRENT_EPOCH_-time 
			END)/3600 
		time 
	from times 
	where (tag='start' or tag='stop') 
	group by taskid) 
	hoursworked 
	on id = hoursworked.taskid 
	"""

class workframe:

	requires = ["select"]
	place = 52

	selcols = """
		CASE
			WHEN workframe.time < 0
			THEN 
				CASE
					WHEN _ENDTIME_ < _CURRENT_EPOCH_
					THEN (workframe.time + _ENDTIME_-_STARTTIME_)/3600
					ELSE (workframe.time + _CURRENT_EPOCH_-_STARTTIME_)/3600
				END
			ELSE workframe.time/3600
		END frametime
	"""

	selfrom = """
	left join (
	select 
		taskid, 
			sum(CASE 
				WHEN tag='stop' 
				THEN 
					CASE
						WHEN _ENDTIME_ < _CURRENT_EPOCH_
						THEN time-_ENDTIME_ 
						ELSE time-_CURRENT_EPOCH_
					END
				ELSE 
					CASE
						WHEN _ENDTIME_ < _CURRENT_EPOCH_
						THEN _ENDTIME_-time 
						ELSE _CURRENT_EPOCH_-time
					END
			END) 
		time 
	from times 
	where ((tag='start' and time <= _ENDTIME_ and time >= _STARTTIME_) or 
		   (tag='stop' and time <= _ENDTIME_ and time > _STARTTIME_)) 
	group by taskid) 
	workframe 
	on id = workframe.taskid 
	"""
	
class rawwork:
	
	requires = ["select"]
	place = 53
	selcols = """
		tag, 
		datetime(rawwork.time,'unixepoch','localtime')
	"""

	selfrom = """
	left join (
	select
		taskid, 
		tag,
		time
	from times
	where ((tag='start' and time <= _ENDTIME_ and time >= _STARTTIME_) or 
		   (tag='stop' and time <= _ENDTIME_ and time > _STARTTIME_))) 
	rawwork
	on id = rawwork.taskid
	"""

class due:
	
	requires = ["select"]
	place = 60
	selcols =  """
		due.time 
	"""

	selfrom = """
	left join (
	select 
		taskid, 
		tag, 
		time 
	from times 
	where tag = 'due') 
	due 
	on id = due.taskid 
	"""

class schedule:
	
	requires = ["select"]
	place = 70
	selcols =  """
		schedstart.time, 
		schedstop.time 
	"""

	selfrom = """
	left join (
	select 
		taskid, 
		tag, 
		time 
	from times 
	where tag = 'schedstart') 
	schedstart 
	on id = schedstart.taskid 
	left join (
	select 
		taskid, 
		tag, 
		time 
	from times 
	where tag = 'schedstop') 
	schedstop 
	on id = schedstop.taskid 
	"""

class startstop:
	
	requires = ["select"]
	place = 80
	selcols =  """
		start.time, 
		stop.time,
		done.time
	"""

	selfrom = """
	left join (
	select 
		taskid, 
		time 
	from times 
	where tag = 'start' and time in (
		select 
			max(time) 
		from times 
		where tag = 'start' 
		group by taskid)
	) 
	start 
	on id = start.taskid 
	left join (
	select 
		taskid, 
		time 
	from times 
	where tag = 'stop' and time in (
		select 
			max(time) 
		from times 
		where tag = 'stop' 
		group by taskid)
	) 
	stop 
	on id = stop.taskid 
	left join (
	select 
		taskid, 
		time 
	from times 
	where tag = 'done' and time in (
		select 
			max(time) 
		from times 
		where tag = 'done' 
		group by taskid)
	) 
	done 
	on id = done.taskid 
	"""

class active:
	
	requires = ["startstop","select"]
	place = 90
	selcols =  """
	CASE 
		WHEN start.time >= stop.time or 
			(start.time is not null and stop.time is null) 
		THEN 1 
		ELSE 0 
	END 
	active
	"""

class done:

	requires = ["startstop","select"]
	place = 100
	selcols = """
	CASE 
		WHEN done.time is not null and 
			(done.time>start.time and done.time>stop.time) 
		THEN 1 
		ELSE 0 
	END 
	done 
	"""

class timetospare:

	requires = ["schedule","worked","estimate","select","due"]
	place = 110
	selcols = """
	CASE 
		WHEN schedstart.time is null
		THEN
			round((
					julianday(due.time,'unixepoch','localtime') -
					julianday('now')
				  ) - max(0,
					CASE
						WHEN worked.time is null
						THEN estimate.time/(3600*0.8)
						ELSE (estimate.time-worked.time)/(3600*08)
					END
				 )
			)
		ELSE
			0
		END
	"""

class timefactor:

	requires = ["schedule","worked","estimate","select","due"]
	place = 110
	selcols = """
	1*round(CASE 
		WHEN schedstart.time is null
		THEN
			(4*
				(240-
					round(
						max(-10,
				 			( julianday(due.time,'unixepoch','localtime') - 
							(julianday('now'))
						) - 
						max(0,
							CASE 
				 		 		WHEN worked.time is null 
								THEN estimate.time/(3600.0*8) 
								ELSE (estimate.time - worked.time)/(3600.0*8) 
						  	END)
						)
					)
				)
			)/10
		ELSE
			(4*
				(250-
					(julianday(schedstart.time,'unixepoch','localtime') - 
					 julianday('now'))
				)
			)/10
	END) 
	timefactor
	"""

class priorityfactor:

	requires = ["priority","difficulty","select"]
	place = 120
	selcols = """
	CASE 
		WHEN priority.value is null 
		THEN round((100*difficulty.value)/2) 
		ELSE round(((333*priority.value)+(100*difficulty.value))/2) 
	END 
	priorityfactor 
	"""

